package jhta;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JInternalFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.SwingConstants;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import javax.swing.ImageIcon;

import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;

public class ReturnUpdate extends JInternalFrame {
	DefaultTableModel model;
	MyTableRender render;
	TableRowSorter<TableModel> tSoter;	
	Connection con;
	
	private int row=-1; // 테이블의 선택된 행의 숫자를 저장하는 변수. 
	private Vector<RentVo> rentSql;
	
	private JComboBox comboBox;
	private JTextField search;
	private JButton btnSearch;
	private JScrollPane scrollPane;
	private JPanel panel;
	private JLabel label_1;
	private JTextField bookTitle;
	private JLabel label_2;
	private JTextField rentName;
	private JLabel label_3;
	private JTextField rentDay;
	private JLabel label_4;
	private JTextField returnExpect;
	private JLabel label_5;
	private JTextField returnDay;
	private JLabel label_6;
	private JTable table;


	public ReturnUpdate() {
		super("반납 취소",true, true, true, true);
		setDefaultCloseOperation(JInternalFrame.HIDE_ON_CLOSE);
		setBounds(807, 100, 707, 505);
		getContentPane().setLayout(null);
		getContentPane().add(getComboBox_1());
		getContentPane().add(getTextField_6());
		getContentPane().add(getButton_1());
		getContentPane().add(getScrollPane_1());
		getContentPane().add(getPanel_1());
		con = JavaDBConnection.getConn();
		
		//테이블 Column 사이즈 조절.
		table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		table.getColumnModel().getColumn(0).setPreferredWidth(40);
		table.getColumnModel().getColumn(1).setPreferredWidth(150);
		table.getColumnModel().getColumn(2).setPreferredWidth(82);
		table.getColumnModel().getColumn(3).setPreferredWidth(150);
		table.getColumnModel().getColumn(4).setPreferredWidth(240);
		
		
	}
	private JComboBox getComboBox_1() {
		if (comboBox == null) {
			String[] t = {"검색조건","전체","대여자","책 제목","날짜"};
			
			comboBox = new JComboBox(t);
			comboBox.addItemListener(new ItemListener() {
				public void itemStateChanged(ItemEvent ie) {
					String check = (String)ie.getItem();
					if(check.equals("날짜")){
						SimpleDateFormat sd = new SimpleDateFormat("yy/MM/dd");
						search.setText("날짜 검색은 yy/mm/dd 형식으로 해주세요. ex) 오늘 -> "+sd.format(new Date()));
					}else if(check.equals("전체")){
						search.setText("전체 검색은 검색어와 상관없이, 최근 반납된 순서로 모두 검색됩니다.");
					}else if(check.equals("검색조건")){
						search.setText("검색조건을 선택해주세요, 미선택시 조건은 '전체'와 동일합니다.");
					}else if(check.equals("책 제목")){
						search.setText("책 제목을 입력해주세요. 제목의 일부분으로도 검색 가능합니다.");
					}else if(check.equals("대여자")){
						search.setText("대여자 이름을 입력해주세요.");
					}
				}
			});			
			comboBox.setBounds(12, 12, 132, 21);
		}
		return comboBox;
	}
	private JTextField getTextField_6() {
		if (search == null) {
			search = new JTextField();
			search.addKeyListener(new KeyAdapter() {
				@Override
				public void keyPressed(KeyEvent e) {
					rentSearch();
				}
			});
			search.addMouseListener(new MouseAdapter() {
				@Override
				public void mouseClicked(MouseEvent e) {
					search.setText("");
				}
			});
			search.setColumns(10);
			search.setBounds(156, 10, 415, 25);
		}
		return search;
	}
	private JButton getButton_1() {
		if (btnSearch == null) {
			btnSearch = new JButton("검색");
			btnSearch.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {
					rentSearch();
				}
			});
			btnSearch.setBounds(583, 11, 97, 23);
		}
		return btnSearch;
	}
	private JScrollPane getScrollPane_1() {
		if (scrollPane == null) {
			scrollPane = new JScrollPane();
			scrollPane.setBounds(12, 40, 668, 279);
			scrollPane.setViewportView(getTable_1());
		}
		return scrollPane;
	}
	private JPanel getPanel_1() {
		if (panel == null) {
			panel = new JPanel();
			panel.setLayout(null);
			panel.setBounds(12, 329, 668, 138);
			panel.add(getLabel_1_1());
			panel.add(getTextField_1_1());
			panel.add(getLabel_2_1());
			panel.add(getTextField_2_1());
			panel.add(getLabel_3_1());
			panel.add(getTextField_3_1());
			panel.add(getLabel_4_1());
			panel.add(getTextField_4_1());
			panel.add(getLabel_5_1());
			panel.add(getTextField_5_1());
			panel.add(getLabel_6_1());
		}
		return panel;
	}
	private JLabel getLabel_1_1() {
		if (label_1 == null) {
			label_1 = new JLabel("제목");
			label_1.setHorizontalAlignment(SwingConstants.RIGHT);
			label_1.setBounds(12, 5, 62, 15);
		}
		return label_1;
	}
	private JTextField getTextField_1_1() {
		if (bookTitle == null) {
			bookTitle = new JTextField();
			bookTitle.setEditable(false);
			bookTitle.setColumns(10);
			bookTitle.setBounds(86, 0, 582, 25);
		}
		return bookTitle;
	}
	private JLabel getLabel_2_1() {
		if (label_2 == null) {
			label_2 = new JLabel("대여자");
			label_2.setHorizontalAlignment(SwingConstants.RIGHT);
			label_2.setBounds(12, 33, 62, 15);
		}
		return label_2;
	}
	private JTextField getTextField_2_1() {
		if (rentName == null) {
			rentName = new JTextField();
			rentName.setEditable(false);
			rentName.setColumns(10);
			rentName.setBounds(86, 28, 141, 25);
		}
		return rentName;
	}
	private JLabel getLabel_3_1() {
		if (label_3 == null) {
			label_3 = new JLabel("대여일자");
			label_3.setHorizontalAlignment(SwingConstants.RIGHT);
			label_3.setBounds(12, 61, 62, 15);
		}
		return label_3;
	}
	private JTextField getTextField_3_1() {
		if (rentDay == null) {
			rentDay = new JTextField();
			rentDay.setEditable(false);
			rentDay.setColumns(10);
			rentDay.setBounds(86, 56, 141, 25);
		}
		return rentDay;
	}
	private JLabel getLabel_4_1() {
		if (label_4 == null) {
			label_4 = new JLabel("반납예정일");
			label_4.setHorizontalAlignment(SwingConstants.RIGHT);
			label_4.setBounds(12, 89, 62, 15);
		}
		return label_4;
	}
	private JTextField getTextField_4_1() {
		if (returnExpect == null) {
			returnExpect = new JTextField();
			returnExpect.setEditable(false);
			returnExpect.setColumns(10);
			returnExpect.setBounds(86, 84, 141, 25);
		}
		return returnExpect;
	}
	private JLabel getLabel_5_1() {
		if (label_5 == null) {
			label_5 = new JLabel("반납일");
			label_5.setHorizontalAlignment(SwingConstants.RIGHT);
			label_5.setBounds(12, 117, 62, 15);
		}
		return label_5;
	}
	private JTextField getTextField_5_1() {
		if (returnDay == null) {
			returnDay = new JTextField();			
			returnDay.setEditable(false);
			returnDay.setColumns(10);
			returnDay.setBounds(86, 112, 141, 25);
		}
		return returnDay;
	}
	private JLabel getLabel_6_1() {
		if (label_6 == null) {
			label_6 = new JLabel("");
			label_6.addMouseListener(new MouseAdapter() {
				@Override
				public void mouseClicked(MouseEvent e) {
					if(row==-1){
						JOptionPane.showMessageDialog(null, "검색 하신 후, 테이블에서 취소하려는 행을 선택해주세요.");
					}else{
						returnCancel();
					}					
				}
			});
			label_6.setIcon(new ImageIcon(ReturnUpdate.class.getResource("/jhta/ImageFolder/cancel.png")));
			label_6.setBounds(233, 28, 435, 109);
		}
		return label_6;
	}
	private JTable getTable_1() {
		if (table == null) {
			String[] t = {"No","반납일","대여자","대여일","도서제목"};
			model = new DefaultTableModel(t, 0);
			table = new JTable(model);
			table.addMouseListener(new MouseAdapter() {
				@Override
				public void mouseClicked(MouseEvent e) {
					row = table.getSelectedRow();

					bookTitle.setText(String.valueOf(table.getValueAt(row, 3)));
					rentName.setText(String.valueOf(table.getValueAt(row, 1)));
					rentDay.setText(String.valueOf(table.getValueAt(row, 2)));
					returnExpect.setText(String.valueOf(table.getValueAt(row, 0)));
					
					//반납일을 가져오기위한 SQL
					String sql = "select * from rent where no="+rentSql.get(row).getrRentNo();
					try{
						PreparedStatement pstmt = con.prepareStatement(sql);
						ResultSet rs = pstmt.executeQuery();
						while(rs.next()){
							returnDay.setText(String.valueOf(rs.getDate("ddate")));							
						}
						rs.close();
						pstmt.close();
					}catch(Exception ex){
						ex.printStackTrace();
					}
				}
			});
			tSoter = new TableRowSorter(model);
			table.setRowSorter(tSoter);
		}
		
		return table;
	}
	public void rentSearch(){
		String combo_selected= (String)comboBox.getSelectedItem();
		String sql_search = "";
		String sql="";
		//이전에 선택 된 테이블의 row 정보를 초기화
		row=-1;
		//테이블 초기화			
		for(int i=model.getRowCount()-1;i>=0;i--){
			model.removeRow(i);
		}
		
		if(combo_selected.equals("전체")||combo_selected.equals("검색조건")){			
			sql_search = "select * from rent where ddate is not null order by ddate desc";
			makeSql(sql_search);
			//tableSet(sql_search);
		}else if(combo_selected.equals("책 제목")){			
			sql_search = "select * from rent inner join book on rent.book_no=book.no where book.name like '%"+search.getText().trim()+"%' and rent.ddate is not null order by ddate desc";
			makeSql(sql_search);			
		}else if(combo_selected.equals("대여자")){
			sql_search = "select * from rent inner join bookmember on rent.member_no=bookmember.no where bookmember.name='"+search.getText().trim()+"' and rent.ddate is not null order by ddate desc";
			makeSql(sql_search);
		}else if(combo_selected.equals("날짜")){
			sql_search = "select * from rent where (to_char(ldate) like '%"+search.getText()+"%' or to_char(edate) like '%"+search.getText()+"%') and ddate is not null order by ddate desc";
			makeSql(sql_search);
		}
		
		if(model.getRowCount()==0){
			JOptionPane.showMessageDialog(null, "검색된 도서대여 정보가 없습니다.");			
		}		
	}
	public void makeSql(String sql){		
		try{
			// 데이터를 역으로 접근할 때 필요한 정보들을 저장하기 위해 RentVo 를 사용하고 Vector<RentVo> 를 저장.
			rentSql = new Vector<>();
			PreparedStatement pstmt = con.prepareStatement(sql);			
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){				
				Vector rowdata = new Vector<>();
				//대여번호
				rowdata.add(rs.getInt("no"));
				//반납일
				rowdata.add(rs.getString("ddate"));
				//대여자
				rowdata.add(findRentor(rs.getInt("member_no")));
				//대여일
				rowdata.add(rs.getString("ldate"));
				//도서제목
				rowdata.add(findBook(rs.getInt("book_no")));				
				model.addRow(rowdata);
				
				// 데이터를 역으로 접근할 때 필요한 정보들을 저장하기 위해 RentVo 를 사용하고 Vector<RentVo> 를 저장.				
				rentSql.add(new RentVo(rs.getInt("No"), rs.getInt("book_no"), rs.getInt("member_no")));
			}
			rs.close();
			pstmt.close();
		}catch(Exception ex){
			ex.printStackTrace();			
		}
	}
	public String findRentor(int member_num){
		String str="";
		String sql = "select name from bookmember where no="+String.valueOf(member_num);
		try{
			PreparedStatement pst = con.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			rs.next();
			str=rs.getString("name");
			rs.close();
			pst.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return str;
	}
	public String findBook(int book_num){
		String str="";
		String sql = "select name from book where no="+String.valueOf(book_num);
		try{
			PreparedStatement pst = con.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			rs.next();
			str=rs.getString("name");
			rs.close();
			pst.close();
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return str;
	}	
	public void returnCancel(){
		String returnSql = "update rent set ddate=null where ddate is not null and no=? and member_no=? and book_no=?";
		try{
			PreparedStatement pstmt_return = con.prepareStatement(returnSql);
			//검색시 저장된 대여도서의 정보를 담은 Vector에 접근하여, 대여번호, 회원번호, 책번호를 가져와 SQL문을 완성하고, 검색하여 반납처리합니다.
			Iterator<RentVo> it = rentSql.iterator(); 
			while(it.hasNext()){				
				RentVo rv_it = it.next();
				if((int)table.getValueAt(row, 0)==rv_it.getrRentNo()){
					pstmt_return.setInt(1, rv_it.getrRentNo());
					pstmt_return.setInt(2, rv_it.getmCode());
					pstmt_return.setInt(3, rv_it.getbCode());
					pstmt_return.executeUpdate();
					String returnBookVolumn = "update book set volume=(volume-1) where no='"+rv_it.getbCode()+"'";
					PreparedStatement psRBV = con.prepareStatement(returnBookVolumn);
					psRBV.executeUpdate();
					psRBV.close();
				}
			}
			//테이블 초기화						
			for(int i=model.getRowCount()-1;i>=0;i--){
				model.removeRow(i);
			}
			
			//검색정보 저장한 Vector 초기화
			rentSql = new Vector<>();
			
			//텍스트필드 초기화			
			bookTitle.setText("");
			rentName.setText("");
			rentDay.setText("");
			returnExpect.setText("");
			
			//선택된 테이블 번호 초기화
			row=-1;
			
			pstmt_return.close();
			
			JOptionPane.showMessageDialog(null, "정상적으로 반납취소 처리 되었습니다.");
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
	}
}
